var _mysql = require('mysql');

var HOST = 'localhost';
var PORT = 3306;
var MYSQL_USER = 'uxperf';
var MYSQL_PASS = 'uxperf';
var DATABASE = 'uxperf';
var DATAPKGS = 'datapkgs'; 		// original table with JSON data from WebStorage
var UXPERFAN = 'uxperfAN'; 		// table containing just id, data of each measurement 
var DATAAN = 'dataAN'; 			// table containing  navigation-timing attributes of single measurement

var mysql = _mysql.createClient({
    host: HOST,
    port: PORT,
    user: MYSQL_USER,
    password: MYSQL_PASS,
});

mysql.query('use ' + DATABASE);

// fetching data from table with JSON
mysql.query('select * from ' + DATAPKGS, function(err, result, fields) {
    if (err) throw err;
    else {			
		mysql.query(
			'DROP TABLE IF EXISTS dataAN'
		);
		mysql.query(
			'DROP TABLE IF EXISTS uxperfAN'			
		); 
		// creating table 'uxperfAN'
		mysql.query(
			'CREATE TABLE IF NOT EXISTS uxperfAN ('+
				'id INT NOT NULL,'+
				'day VARCHAR(255),'+
			'PRIMARY KEY(id)) character set utf8'
		);
		// creating table 'dataAN'
		mysql.query(
			'CREATE TABLE IF NOT EXISTS dataAN ('+
				'id INT NOT NULL AUTO_INCREMENT,'+
				'event_id INT NOT NULL,'+
				'uxperfAN_id INT,'+
				'type VARCHAR(255), '+
				'timestamp BIGINT, '+
				'navigationStart BIGINT, '+
				'unloadEventStart BIGINT, '+
				'unloadEventEnd BIGINT, '+
				'fetchStart BIGINT, '+
				'domainLookupStart BIGINT, '+
				'domainLookupEnd BIGINT, '+
				'connectStart BIGINT, '+
				'connectEnd BIGINT, '+
				'requestStart BIGINT, '+
				'responseStart BIGINT, '+
				'responseEnd BIGINT, '+
				'domLoading BIGINT, '+
				'domInteractive BIGINT, '+
				'domContentLoadedEventStart BIGINT, '+
				'domContentLoadedEventEnd BIGINT, '+
				'domComplete BIGINT, '+
				'loadEventStart BIGINT, '+
				'loadEventEnd BIGINT, '+
				'navigation INT, ' +
				'location VARCHAR(255), '+
				'refferer VARCHAR(255), '+
				'time_hms VARCHAR(10), '+
			'PRIMARY KEY (id), ' +
			'FOREIGN KEY (uxperfAN_id) REFERENCES uxperfAN(id) ON DELETE CASCADE)'
		);

        console.log('dane z datapkgs');
        console.log('---------------------------------');
        for (var i in result) {
			var row = result[i];								// single row
			console.log('ID= ' +row.id+ ' DAY= ' +row.day);
			
			var jsonData = JSON.parse(row.data); 				// field with JSON data
			
			mysql.query('insert into '+ UXPERFAN +' (id, day) values ("' 
				+ row.id + '", "' +row.day+ '")',
			  function selectCb(err, results, fields) {
				 if (err) throw err;
				 //else console.log('inserted into uxperfAN');
			});
			
			//console.log('ITEMS=' +jsonData.items);
			
			for (var i=0; i<jsonData.items.length; i++) {					
				var item = JSON.parse(jsonData.items[i]);						
				 
				console.log('item[id=' + item.id+']');
				console.log('   type: \t' + item.type);
				console.log('   timestamp: \t' + item.timestamp);
				console.log('   navigationStart: \t' + item.timing.navigationStart);	
				console.log('   unloadEventStart: \t' + item.timing.unloadEventStart);	
				console.log('   unloadEventEnd: \t' + item.timing.unloadEventEnd);	
				console.log('   fetchStart: \t\t' + item.timing.fetchStart);	
				console.log('   domainLookupStart: \t' + item.timing.domainLookupStart);	
				console.log('   domainLookupEnd: \t' + item.timing.domainLookupEnd);	
				console.log('   connectStart: \t' + item.timing.connectStart);	
				console.log('   connectEnd: \t\t' + item.timing.connectEnd);	
				console.log('   requestStart: \t' + item.timing.requestStart);	
				console.log('   responseStart: \t' + item.timing.responseStart);	
				console.log('   responseEnd: \t' + item.timing.responseEnd);	
				console.log('   domLoading: \t\t' + item.timing.domLoading);	
				console.log('   domInteractive: \t' + item.timing.domInteractive);	
				console.log('   domContLoadedEvtSt: \t' + item.timing.domContentLoadedEventStart);	
				console.log('   domContLoadedEvEnd: \t' + item.timing.domContentLoadedEventEnd);	
				console.log('   domComplete: \t' + item.timing.domComplete);	
				console.log('   loadEventStart: \t' + item.timing.loadEventStart);	
				console.log('   loadEventEnd: \t' + item.timing.loadEventEnd);	
				
				console.log('   navigation type: \t' + item.navigation.type);	
				console.log('   location: \t' + item.navigation.location);	
				console.log('   referrer: \t' + item.navigation.referrer);	
				
				// setting 'undefined' values as -1
				if( item.timing.navigationStart == undefined )   
					item.timing.navigationStart = -1;
					
				if( item.timing.unloadEventStart == undefined )
					item.timing.unloadEventStart = -1; 
					
				if( item.timing.unloadEventEnd == undefined )
					item.timing.unloadEventEnd = -1; 

				if( item.timing.fetchStart == undefined )
					item.timing.fetchStart = -1; 

				if( item.timing.domainLookupStart == undefined )
					item.timing.domainLookupStart = -1; 

				if( item.timing.domainLookupEnd == undefined )
					item.timing.domainLookupEnd = -1; 

				if( item.timing.connectStart == undefined )
					item.timing.connectStart = -1; 

				if( item.timing.connectEnd == undefined )
					item.timing.connectEnd = -1; 

				if( item.timing.requestStart == undefined )
					item.timing.requestStart = -1; 

				if( item.timing.responseStart == undefined )
					item.timing.responseStart = -1; 

				if( item.timing.responseEnd == undefined )
					item.timing.responseEnd = -1; 

				if( item.timing.domLoading == undefined )
					item.timing.domLoading = -1; 

				if( item.timing.domInteractive == undefined )
					item.timing.domInteractive = -1; 
		
				if( item.timing.domContentLoadedEventStart == undefined )
					item.timing.domContentLoadedEventStart = -1; 
			
				if( item.timing.domContentLoadedEventEnd == undefined )
					item.timing.domContentLoadedEventEnd = -1; 	

				if( item.timing.domComplete == undefined )
					item.timing.domComplete = -1; 	

				if( item.timing.loadEventStart == undefined )
					item.timing.loadEventStart = -1; 

				if( item.timing.loadEventEnd == undefined )
					item.timing.loadEventEnd = -1; 			

				if( item.navigation.type == undefined )
					item.navigation.type = -1; 	

				if( item.navigation.location == undefined )
					item.navigation.location = -1; 	

				if( item.navigation.refferer == undefined )
					item.navigation.refferer = -1; 						
					
				// DLA TESTU
				var time_hms = new Date(item.timestamp);				
				var iso = time_hms.toISOString().match(/(\d{2}:\d{2}:\d{2})/);
				console.log("TIME_HMS" + iso[1]);								
								
				// inserting parsed attributes into new table 'dataAN'
				mysql.query('insert into '+ DATAAN
					+ ' (event_id, uxperfAN_id, type, timestamp, navigationStart, unloadEventStart,' 
					+ ' unloadEventEnd, fetchStart, domainLookupStart, domainLookupEnd, connectStart,'
					+ ' connectEnd, requestStart, responseStart, responseEnd, domLoading, domInteractive,'
					+ ' domContentLoadedEventStart, domContentLoadedEventEnd, domComplete, loadEventStart,'
					+ ' loadEventEnd, navigation, location, refferer, time_hms)' 
					+ ' values ("' 
					+ item.id + '", "' 
					+ row.id+ '", "' 
					+ item.type+ '", "' 
					+ item.timestamp + '", "' 
					+ item.timing.navigationStart+ '", "' 
					+ item.timing.unloadEventStart+ '", "' 
					+ item.timing.unloadEventEnd+ '", "' 
					+ item.timing.fetchStart+ '", "'
					+ item.timing.domainLookupStart+ '", "'
					+ item.timing.domainLookupEnd+ '", "'
					+ item.timing.connectStart+ '", "'
					+ item.timing.connectEnd+ '", "'
					+ item.timing.requestStart+ '", "'
					+ item.timing.responseStart+ '", "'
					+ item.timing.responseEnd+ '", "'
					+ item.timing.domLoading+ '", "'
					+ item.timing.domInteractive+ '", "'
					+ item.timing.domContentLoadedEventStart+ '", "'
					+ item.timing.domContentLoadedEventEnd+ '", "'
					+ item.timing.domComplete+ '", "'
					+ item.timing.loadEventStart+ '", "'					
					+ item.timing.loadEventEnd+ '", "'
					+ item.navigation.type+ '", "'
					+ item.navigation.location+ '", "'
					+ item.navigation.refferer+ '", "'
					+ iso[1]+ '")',
				  function selectCb(err, results, fields) {
					if (err) throw err;
					//else console.log('blad dataAN');
				});				
				}
		}console.log('-------------- end --------------');
	}
});

